from openpyxl import Workbook, load_workbook
from itertools import groupby


def read_excel(file_name='input.xlsx'):
    wb = load_workbook(file_name)
    sheet1 = wb.worksheets[0]
    max_row = sheet1.max_row
    rows_iter = sheet1.iter_rows(min_col=1, min_row=3,
                                 max_col=3, max_row=max_row)
    rows_list = [[cell.value for cell in row] for row in rows_iter]
    # print(rows_list)
    return rows_list


def group_merge(rows_list):
    # [['测试', '随机文本1', '随机文本1'], ['测试', '随机文本2', '随机文本2'], ['测试', '随机文本3', '随机文本3'], ['test', 'abc1', 'def1'], ['test', 'abc2', 'def2'], ['test', 'abc3', 'def3'], ['test', 'abc4', 'def4'], ['test', 'abc5', 'def5'], ['test', 'abc6', 'def6']]
    g = groupby(rows_list, lambda r: r[0])
    g = {k: list(g) for k, g in g}
    # {'测试': [['测试', '随机文本1', '随机文本1'], ['测试', '随机文本2', '随机文本2'], ['测试', '随机文本3', '随机文本3']], 'test': [['test', 'abc1', 'def1'], ['test', 'abc2', 'def2'], ['test', 'abc3', 'def3'], ['test', 'abc4', 'def4'], ['test', 'abc5', 'def5'], ['test', 'abc6', 'def6']]}
    # print(g)
    return g


def write_excel(g, file_name='output.xlsx'):
    wb = Workbook()
    ws = wb.active
    ws.cell(row=1, column=1).value = 'query'
    ws.cell(row=1, column=2).value = 'result1'
    ws.cell(row=1, column=3).value = 'result2'
    row_number = 1
    for k, v in g.items():
        row_number += 1
        ws.cell(row=row_number, column=1).value = k
        ws.cell(row=row_number, column=2).value = ','.join([l[1] for l in v])
        ws.cell(row=row_number, column=3).value = ','.join([l[2] for l in v])
    wb.save(file_name)


if __name__ == "__main__":
    rows_list = read_excel()
    g = group_merge(rows_list)
    write_excel(g)
